package org.goldfish.message_board01.dao;

import org.goldfish.message_board01.entity.MessagePreview;
import org.goldfish.message_board01.util.DatabaseConnectionManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MessagePreviewDAO extends AbstractDAO {

    public MessagePreviewDAO() {
        this.dbManager = new DatabaseConnectionManager();
    }

    public List<MessagePreview> getAllMessages() {
        List<MessagePreview> messages = new ArrayList<>();
        String query = "SELECT id, sender, title, preview, time FROM message_preview";
        try {
            Connection conn = dbManager.getConnection();
            try (
                    PreparedStatement stmt = conn.prepareStatement(query);
                    ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    MessagePreview message = new MessagePreview(
                            rs.getInt("id"),
                            rs.getString("sender"),
                            rs.getString("title"),
                            rs.getString("preview"),
                            rs.getTimestamp("time").toLocalDateTime()
                    );
                    messages.add(message);
                }
            }
            dbManager.releaseConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    public MessagePreview getMessageById(int id) {
        String query = "SELECT * FROM message_preview WHERE id = ?";
        try {
            Connection conn = dbManager.getConnection();
            try (
                    PreparedStatement stmt = conn.prepareStatement(query)) {
                stmt.setInt(1, id);
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next()) {
                        return new MessagePreview(
                                rs.getInt("id"),
                                rs.getString("sender"),
                                rs.getString("title"),
                                rs.getString("preview"),
                                rs.getTimestamp("time").toLocalDateTime()
                        );
                    }
                }
            }
            dbManager.releaseConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int insertMessagePreview(MessagePreview messagePreview) {
        String query = "INSERT INTO message_preview (sender, title, preview, time) VALUES (?, ?, ?, ?)";
        try (Connection conn = dbManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, messagePreview.getSender());
            stmt.setString(2, messagePreview.getTitle());
            stmt.setString(3, messagePreview.getPreview());
            stmt.setTimestamp(4, java.sql.Timestamp.valueOf(messagePreview.getTime()));
            int affectedRows = stmt.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        return generatedKeys.getInt(1);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    public int insertMessagePreviewAndGetId(MessagePreview messagePreview) {
        String insertQuery = "INSERT INTO message_preview (id, sender, title, preview, time) VALUES (?, ?, ?, ?, ?)";
        String selectMaxIdQuery = "SELECT MAX(id) AS max_id FROM message_preview";
        try {
            Connection conn = dbManager.getConnection();
            try (
                    PreparedStatement insertStmt = conn.prepareStatement(insertQuery);
                    PreparedStatement selectMaxIdStmt = conn.prepareStatement(selectMaxIdQuery)
            ) {
                int idToUse = -1;
                try (ResultSet rs = selectMaxIdStmt.executeQuery()) {
                    if (rs.next()) {
                        idToUse = rs.getInt("max_id") + 1;
                    }
                }
                if (idToUse == -1) {
                    return -1;
                }
                insertStmt.setInt(1, idToUse);
                insertStmt.setString(2, messagePreview.getSender());
                insertStmt.setString(3, messagePreview.getTitle());
                insertStmt.setString(4, messagePreview.getPreview());
                insertStmt.setTimestamp(5, java.sql.Timestamp.valueOf(messagePreview.getTime()));
                int affectedRows = insertStmt.executeUpdate();
                if (affectedRows > 0) {
                    return idToUse;
                }
            }
            dbManager.releaseConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }
}